﻿using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using Server.OptExp;
using System;
using System.Data.Common;

namespace Server.Framework.Service
{
    /// <summary>
    /// Author: Leil
    /// Description: 数据库操作类
    /// CreateTime: 2020/5/19 14:54:58
    /// </summary>
    class DBService
    {
        private static DBService instance;

        private static readonly object obj = new object();

        private readonly string connString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));User ID=outbound;Password=123456";

        private readonly DbConnection connection;

        private DBService()
        {
            try
            {
                connection = new OracleConnection(connString);
            }
            catch (Exception e)
            {
                Console.WriteLine("Create connection error:{0}", e.Message);
            }
        }

        public static DBService Instance()
        {
            if (instance == null)
            {
                lock(obj)
                {
                    if (instance == null)
                    {
                        instance = new DBService();
                    }
                }
            }
            return instance;
        }

        /// <summary>
        /// 获取数据库连接对象
        /// </summary>
        /// <returns>连接对象</returns>
        public DbConnection GetConnection()
        {
            return this.connection;
        }


        /// <summary>
        /// 执行查询操作
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="parameters">参数列表</param>
        /// <param name="action">返回值代理</param>
        public void ExecuteQuery(string sql, Action<DbDataReader> action, DbParameter[] parameters = null)
        {
            try
            {
                connection.Open();
                using (DbCommand command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    if (parameters != null && parameters.Length > 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    using (DbDataReader reader = command.ExecuteReader())
                    {
                        action(reader);
                    }
                }
                connection.Close();
            }
            catch (Exception e)
            {

                Console.WriteLine("Query Error:{0}", e.Message);
                throw new CommonOptException(e.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// 执行非查询sql
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="action">返回参数代理</param>
        /// <param name="parameters">查询参数</param>
        public void ExecuteNonQuery(string sql, Action<int> action, DbParameter[] parameters = null)
        {
            try
            {
                connection.Open();
                using (DbCommand command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    if (parameters != null && parameters.Length > 0)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    int res = command.ExecuteNonQuery();
                    action(res);
                }
                connection.Close();
            }
            catch (Exception e)
            {

                Console.WriteLine("Query Error:{0}", e.Message);
                throw new CommonOptException(e.Message);
            }
            finally
            {
                connection.Close();
            }
        }






        public void ExecuteProcedure()
        {
            try
            {
                connection.Open();
                using(DbCommand command = connection.CreateCommand())
                {
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.CommandText = "ADDUSERANDALLOTROLE";
                    OracleParameter p1 = new OracleParameter("loginName", OracleDbType.Varchar2);
                    p1.Value = "hahahah";
                    p1.Direction = System.Data.ParameterDirection.Input;
                    OracleParameter p2 = new OracleParameter("loginPwd", OracleDbType.Varchar2);
                    p2.Value = "123456";
                    p2.Direction = System.Data.ParameterDirection.Input;
                    OracleParameter p3 = new OracleParameter("roleId", OracleDbType.Int32);
                    p3.Value = 200;
                    p3.Direction = System.Data.ParameterDirection.Input;
                    command.Parameters.Add(p1);
                    command.Parameters.Add(p2);
                    command.Parameters.Add(p3);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {

                Console.WriteLine(e.Message);
            }
            finally
            {
                connection.Close();
            }
        }





        public void ExecuteProcedureOut()
        {
            try
            {
                connection.Open();
                using (DbCommand command = connection.CreateCommand())
                {
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.CommandText = "GETSEQCURRVAL";
                    OracleParameter p1 = new OracleParameter("id", OracleDbType.Int32);
                    p1.Direction = System.Data.ParameterDirection.Output;
                    command.Parameters.Add(p1);
                    command.ExecuteNonQuery();
                    Console.WriteLine(p1.Value);
                }
            }
            catch (Exception e)
            {

                Console.WriteLine(e.Message);
            }
            finally
            {
                connection.Close();
            }
        }


    }
}
